<!DOCTYPE html>
<html lang=zh>
<head><meta name="generator" content="Hexo 3.9.0">
    <!-- so meta -->
    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="HandheldFriendly" content="True">
    <meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1">
    <meta name="description" content="前言最近在公司做一个日志统计的小后台，在拿到混乱的日志之后，将日志分解并储存到 MySQL 中。这一步起始早早就完成了，接下来是使用 koa 新建接口，对数据库做增删查改。下面记录下自己在使用 mysql 查询的遇到的一些问题。 MySQL 如何对表中数据进行分组需求是这样的，有一个记录日志表–logs，以及一个记录用户的表 user，在 logs 中有一个外键指向 user 表的 id 字段。现">
<meta name="keywords" content="数据库">
<meta property="og:type" content="article">
<meta property="og:title" content="mysql查询的小心得">
<meta property="og:url" content="https://andyliwr.github.io/2017/11/08/mysql_query_learning/index.html">
<meta property="og:site_name" content="月光倾城的代码小屋">
<meta property="og:description" content="前言最近在公司做一个日志统计的小后台，在拿到混乱的日志之后，将日志分解并储存到 MySQL 中。这一步起始早早就完成了，接下来是使用 koa 新建接口，对数据库做增删查改。下面记录下自己在使用 mysql 查询的遇到的一些问题。 MySQL 如何对表中数据进行分组需求是这样的，有一个记录日志表–logs，以及一个记录用户的表 user，在 logs 中有一个外键指向 user 表的 id 字段。现">
<meta property="og:locale" content="zh-CN">
<meta property="og:image" content="http://fs.andylistudio.com/1510133197210.png">
<meta property="og:updated_time" content="2020-01-15T06:30:09.120Z">
<meta name="twitter:card" content="summary">
<meta name="twitter:title" content="mysql查询的小心得">
<meta name="twitter:description" content="前言最近在公司做一个日志统计的小后台，在拿到混乱的日志之后，将日志分解并储存到 MySQL 中。这一步起始早早就完成了，接下来是使用 koa 新建接口，对数据库做增删查改。下面记录下自己在使用 mysql 查询的遇到的一些问题。 MySQL 如何对表中数据进行分组需求是这样的，有一个记录日志表–logs，以及一个记录用户的表 user，在 logs 中有一个外键指向 user 表的 id 字段。现">
<meta name="twitter:image" content="http://fs.andylistudio.com/1510133197210.png">
    
    
        
          
              <link rel="shortcut icon" href="/images/favicon.ico">
          
        
        
          
            <link rel="icon" type="image/png" href="/images/favicon-192x192.png" sizes="192x192">
          
        
        
          
            <link rel="apple-touch-icon" sizes="180x180" href="/images/apple-touch-icon.png">
          
        
    
    <!-- title -->
    <title>mysql查询的小心得</title>
    <!-- styles -->
    <link rel="stylesheet" href="/css/style.css">
    <!-- persian styles -->
    
      <link rel="stylesheet" href="/css/rtl.css">
    
    <!-- rss -->
    
    
</head>

<body class="max-width mx-auto px3 ltr">
    
      <div id="header-post">
  <a id="menu-icon" href="#"><i class="fas fa-bars fa-lg"></i></a>
  <a id="menu-icon-tablet" href="#"><i class="fas fa-bars fa-lg"></i></a>
  <a id="top-icon-tablet" href="#" onclick="$('html, body').animate({ scrollTop: 0 }, 'fast');" style="display:none;"><i class="fas fa-chevron-up fa-lg"></i></a>
  <span id="menu">
    <span id="nav">
      <ul>
         
          <li><a href="/">首页</a></li>
         
          <li><a href="/archives/">归档</a></li>
         
          <li><a href="/search/">搜索</a></li>
         
          <li><a href="/tags/">标签</a></li>
         
          <li><a href="/others/">其他文章</a></li>
        
      </ul>
    </span>
    <br/>
    <span id="actions">
      <ul>
        
        <li><a class="icon" href="/2017/12/05/travis_nodejs_publish/"><i class="fas fa-chevron-left" aria-hidden="true" onmouseover="$('#i-prev').toggle();" onmouseout="$('#i-prev').toggle();"></i></a></li>
        
        
        <li><a class="icon" href="/2017/11/08/nodejs_identicon/"><i class="fas fa-chevron-right" aria-hidden="true" onmouseover="$('#i-next').toggle();" onmouseout="$('#i-next').toggle();"></i></a></li>
        
        <li><a class="icon" href="#" onclick="$('html, body').animate({ scrollTop: 0 }, 'fast');"><i class="fas fa-chevron-up" aria-hidden="true" onmouseover="$('#i-top').toggle();" onmouseout="$('#i-top').toggle();"></i></a></li>
        <li><a class="icon" href="#"><i class="fas fa-share-alt" aria-hidden="true" onmouseover="$('#i-share').toggle();" onmouseout="$('#i-share').toggle();" onclick="$('#share').toggle();return false;"></i></a></li>
      </ul>
      <span id="i-prev" class="info" style="display:none;">上一篇</span>
      <span id="i-next" class="info" style="display:none;">下一篇</span>
      <span id="i-top" class="info" style="display:none;">返回顶部</span>
      <span id="i-share" class="info" style="display:none;">分享文章</span>
    </span>
    <br/>
    <div id="share" style="display: none">
      <ul>
  <li><a class="icon" href="http://www.facebook.com/sharer.php?u=https://andyliwr.github.io/2017/11/08/mysql_query_learning/"><i class="fab fa-facebook " aria-hidden="true"></i></a></li>
  <li><a class="icon" href="https://twitter.com/share?url=https://andyliwr.github.io/2017/11/08/mysql_query_learning/&text=mysql查询的小心得"><i class="fab fa-twitter " aria-hidden="true"></i></a></li>
  <li><a class="icon" href="http://www.linkedin.com/shareArticle?url=https://andyliwr.github.io/2017/11/08/mysql_query_learning/&title=mysql查询的小心得"><i class="fab fa-linkedin " aria-hidden="true"></i></a></li>
  <li><a class="icon" href="https://pinterest.com/pin/create/bookmarklet/?url=https://andyliwr.github.io/2017/11/08/mysql_query_learning/&is_video=false&description=mysql查询的小心得"><i class="fab fa-pinterest " aria-hidden="true"></i></a></li>
  <li><a class="icon" href="mailto:?subject=mysql查询的小心得&body=Check out this article: https://andyliwr.github.io/2017/11/08/mysql_query_learning/"><i class="fas fa-envelope " aria-hidden="true"></i></a></li>
  <li><a class="icon" href="https://getpocket.com/save?url=https://andyliwr.github.io/2017/11/08/mysql_query_learning/&title=mysql查询的小心得"><i class="fab fa-get-pocket " aria-hidden="true"></i></a></li>
  <li><a class="icon" href="http://reddit.com/submit?url=https://andyliwr.github.io/2017/11/08/mysql_query_learning/&title=mysql查询的小心得"><i class="fab fa-reddit " aria-hidden="true"></i></a></li>
  <li><a class="icon" href="http://www.stumbleupon.com/submit?url=https://andyliwr.github.io/2017/11/08/mysql_query_learning/&title=mysql查询的小心得"><i class="fab fa-stumbleupon " aria-hidden="true"></i></a></li>
  <li><a class="icon" href="http://digg.com/submit?url=https://andyliwr.github.io/2017/11/08/mysql_query_learning/&title=mysql查询的小心得"><i class="fab fa-digg " aria-hidden="true"></i></a></li>
  <li><a class="icon" href="http://www.tumblr.com/share/link?url=https://andyliwr.github.io/2017/11/08/mysql_query_learning/&name=mysql查询的小心得&description="><i class="fab fa-tumblr " aria-hidden="true"></i></a></li>
</ul>

    </div>
    <div id="toc">
      <ol class="toc"><li class="toc-item toc-level-4"><a class="toc-link" href="#前言"><span class="toc-number">1.</span> <span class="toc-text">前言</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#MySQL-如何对表中数据进行分组"><span class="toc-number">2.</span> <span class="toc-text">MySQL 如何对表中数据进行分组</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#表结构"><span class="toc-number">3.</span> <span class="toc-text">表结构</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#SQL-日期格式化函数"><span class="toc-number">4.</span> <span class="toc-text">SQL 日期格式化函数</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#如何做每个分类的次数统计"><span class="toc-number">5.</span> <span class="toc-text">如何做每个分类的次数统计</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#sequelizejs-如何执行原生的-js"><span class="toc-number">6.</span> <span class="toc-text">sequelizejs 如何执行原生的 js</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#参考资料"><span class="toc-number">7.</span> <span class="toc-text">参考资料</span></a></li></ol>
    </div>
  </span>
</div>

    
    <div class="content index py4">
        
        <article class="post" itemscope itemtype="http://schema.org/BlogPosting">
  <header>
    
    <h1 class="posttitle" itemprop="name headline">
        mysql查询的小心得
    </h1>



    <div class="meta">
      <span class="author" itemprop="author" itemscope itemtype="http://schema.org/Person">
        <span itemprop="name">月光倾城的代码小屋</span>
      </span>
      
    <div class="postdate">
        <time datetime="2017-11-08T15:26:51.000Z" itemprop="datePublished">2017-11-08</time>
    </div>


      

      
    <div class="article-tag">
        <i class="fas fa-tag"></i>
        <a class="tag-link" href="/tags/数据库/">数据库</a>
    </div>


    </div>
  </header>
  

  <div class="content" itemprop="articleBody">
    <h4 id="前言"><a href="#前言" class="headerlink" title="前言"></a>前言</h4><p>最近在公司做一个日志统计的小后台，在拿到混乱的日志之后，将日志分解并储存到 MySQL 中。这一步起始早早就完成了，接下来是使用 koa 新建接口，对数据库做增删查改。下面记录下自己在使用 mysql 查询的遇到的一些问题。</p>
<h4 id="MySQL-如何对表中数据进行分组"><a href="#MySQL-如何对表中数据进行分组" class="headerlink" title="MySQL 如何对表中数据进行分组"></a>MySQL 如何对表中数据进行分组</h4><p>需求是这样的，有一个记录日志表–logs，以及一个记录用户的表 user，在 logs 中有一个外键指向 user 表的 id 字段。现在需要查找 logs 表并对 users 表的手机号码—mobile_tel 进行去重。同时拿到 user 和 logs 表中的数据就需要使用到 join，去重需要用到分组 group by<br>group by 意思就是将查询结果按照 1 个或多个字段进行分组，字段值相同的为一组。可用于单个字段分组，也可用于多个字段分组。</p>
<h4 id="表结构"><a href="#表结构" class="headerlink" title="表结构"></a>表结构</h4><p><strong>user 表</strong></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> <span class="keyword">IF</span> <span class="keyword">NOT</span> <span class="keyword">EXISTS</span> <span class="string">`user`</span> (</span><br><span class="line">    <span class="string">`id`</span> <span class="built_in">INTEGER</span> <span class="keyword">NOT</span> <span class="literal">NULL</span> AUTO_INCREMENT,</span><br><span class="line">    <span class="string">`client_id`</span> <span class="built_in">CHAR</span>(<span class="number">10</span>) <span class="keyword">NOT</span> <span class="literal">NULL</span>,</span><br><span class="line">    <span class="string">`mobile_tel`</span> <span class="built_in">CHAR</span>(<span class="number">11</span>) <span class="keyword">NOT</span> <span class="literal">NULL</span> <span class="keyword">UNIQUE</span>,</span><br><span class="line">    <span class="string">`utime`</span> DATETIME <span class="keyword">NOT</span> <span class="literal">NULL</span>,</span><br><span class="line">    <span class="string">`dtime`</span> DATETIME,</span><br><span class="line">    <span class="keyword">UNIQUE</span> <span class="string">`user_mobile_tel_unique`</span> (<span class="string">`mobile_tel`</span>),</span><br><span class="line">    PRIMARY <span class="keyword">KEY</span> (<span class="string">`id`</span>)</span><br><span class="line">)  <span class="keyword">ENGINE</span>=<span class="keyword">INNODB</span>;</span><br></pre></td></tr></table></figure>
<p><strong>logs 表</strong><br>device 是一个设备表</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> <span class="keyword">IF</span> <span class="keyword">NOT</span> <span class="keyword">EXISTS</span> <span class="string">`logs`</span> (</span><br><span class="line">    <span class="string">`id`</span> <span class="built_in">INTEGER</span> <span class="keyword">NOT</span> <span class="literal">NULL</span> AUTO_INCREMENT,</span><br><span class="line">    <span class="string">`aip`</span> <span class="built_in">CHAR</span>(<span class="number">15</span>),</span><br><span class="line">    <span class="string">`qsid`</span> <span class="built_in">CHAR</span>(<span class="number">10</span>) <span class="keyword">NOT</span> <span class="literal">NULL</span>,</span><br><span class="line">    <span class="string">`cannel`</span> <span class="built_in">CHAR</span>(<span class="number">10</span>),</span><br><span class="line">    <span class="string">`atime`</span> DATETIME <span class="keyword">NOT</span> <span class="literal">NULL</span>,</span><br><span class="line">    <span class="string">`system_version`</span> <span class="built_in">CHAR</span>(<span class="number">10</span>) <span class="keyword">NOT</span> <span class="literal">NULL</span>,</span><br><span class="line">    <span class="string">`app_version`</span> <span class="built_in">CHAR</span>(<span class="number">10</span>) <span class="keyword">NOT</span> <span class="literal">NULL</span>,</span><br><span class="line">    <span class="string">`utime`</span> DATETIME <span class="keyword">NOT</span> <span class="literal">NULL</span>,</span><br><span class="line">    <span class="string">`dtime`</span> DATETIME,</span><br><span class="line">    <span class="string">`user_id`</span> <span class="built_in">INTEGER</span>,</span><br><span class="line">    <span class="string">`device_id`</span> <span class="built_in">INTEGER</span>,</span><br><span class="line">    PRIMARY <span class="keyword">KEY</span> (<span class="string">`id`</span>),</span><br><span class="line">    <span class="keyword">FOREIGN</span> <span class="keyword">KEY</span> (<span class="string">`user_id`</span>)</span><br><span class="line">        <span class="keyword">REFERENCES</span> <span class="string">`user`</span> (<span class="string">`id`</span>)</span><br><span class="line">        <span class="keyword">ON</span> <span class="keyword">DELETE</span> <span class="keyword">SET</span> <span class="literal">NULL</span> <span class="keyword">ON</span> <span class="keyword">UPDATE</span> <span class="keyword">CASCADE</span>,</span><br><span class="line">    <span class="keyword">FOREIGN</span> <span class="keyword">KEY</span> (<span class="string">`device_id`</span>)</span><br><span class="line">        <span class="keyword">REFERENCES</span> <span class="string">`device`</span> (<span class="string">`id`</span>)</span><br><span class="line">        <span class="keyword">ON</span> <span class="keyword">DELETE</span> <span class="keyword">SET</span> <span class="literal">NULL</span> <span class="keyword">ON</span> <span class="keyword">UPDATE</span> <span class="keyword">CASCADE</span></span><br><span class="line">)  <span class="keyword">ENGINE</span>=<span class="keyword">INNODB</span>;</span><br></pre></td></tr></table></figure>
<p>根据 mobile_tel 分组查询所有的 log</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> <span class="keyword">logs</span></span><br><span class="line">  <span class="keyword">INNER</span> <span class="keyword">JOIN</span> <span class="keyword">user</span> <span class="keyword">ON</span> user.id = logs.user_id</span><br><span class="line">  <span class="keyword">GROUP</span> <span class="keyword">BY</span> user.mobile_tel</span><br></pre></td></tr></table></figure>
<p>得到的结果如下：<br><img src="http://fs.andylistudio.com/1510133197210.png" alt="分组查询"><br>由于新装的 mysql 默认的 sql_mode 是加上了 ONLY_FULL_GROUP_BY，意思就是说对于 GROUP BY 聚合操作，如果在 SELECT 中的列，没有在 GROUP BY 中出现，那么这个 SQL 是不合法的。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">ERROR 1055 (42000): Expression <span class="comment">#2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.tt.count' which is</span></span><br><span class="line">not functionally dependent on columns in GROUP BY clause; this is incompatible <span class="keyword">with</span> sql_mode=only_full_group_by</span><br></pre></td></tr></table></figure>
<p>关于 MySQL 到底设置 ONLY_FULL_GROUP_BY 好，还是不设置好，可以参考<a href="http://www.ywnds.com/?p=8184" target="_blank" rel="noopener">这篇博客</a><br>因为确实需要用到 NO_FULL_GROUP_BY 分组查询，所以需要在数据库中设置下 sql_mode：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment"># mysql命令行模式</span></span><br><span class="line"><span class="keyword">SET</span> <span class="keyword">GLOBAL</span> sql_mode=(<span class="keyword">SELECT</span> <span class="keyword">REPLACE</span>(@@sql_mode,<span class="string">'ONLY_FULL_GROUP_BY'</span>,<span class="string">''</span>));</span><br></pre></td></tr></table></figure>
<p>这样做有个缺陷，每次服务器重启或者电脑重启都需要重新设置。本来也想设置到 mysql 的配置文件里的，但是设置之后发现 mysql 不能启动了。这一点我也没找到很好的解决方案。</p>
<h4 id="SQL-日期格式化函数"><a href="#SQL-日期格式化函数" class="headerlink" title="SQL 日期格式化函数"></a>SQL 日期格式化函数</h4><p>当一个表存在日期的时候，通常取出来的时候都需要做些格式化，如何在 SQL 做格式化呢？将日期转变成年月日的形式，2017/11/10 16:16:00 变成 2017/11/10</p>
<figure class="highlight pgsql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> <span class="type">DATE</span>(h5log.logs.atime) <span class="keyword">AS</span> atime <span class="keyword">FROM</span> h5log.logs</span><br></pre></td></tr></table></figure>
<p>或者使用 DATE_FORMAT</p>
<figure class="highlight cos"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">SELECT DATE_FORMAT(h5log.logs.utime, '<span class="built_in">%Y</span>-<span class="built_in">%d</span>-<span class="built_in">%m</span> <span class="built_in">%H</span>:<span class="built_in">%i</span>:<span class="built_in">%S</span>') <span class="keyword">AS</span> utime FROM h5log.logs</span><br></pre></td></tr></table></figure>
<p>具体怎么使用请参考<a href="http://www.w3school.com.cn/sql/sql_dates.asp" target="_blank" rel="noopener">这篇文章</a></p>
<h4 id="如何做每个分类的次数统计"><a href="#如何做每个分类的次数统计" class="headerlink" title="如何做每个分类的次数统计"></a>如何做每个分类的次数统计</h4><p>需求如下：需要统计每个错误出现的次数，以及他们各自的占比。起始在错误出现次数不是很多的情况下，先取出所有的数据，然后使用循环做统计完全可以。但是这里我介绍一种直接使用 SQL 做统计的方法—使用 SQL 的条件语句 WHILE-THEN</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span></span><br><span class="line"><span class="keyword">SUM</span>(<span class="keyword">CASE</span> <span class="keyword">WHEN</span> error_id = <span class="number">1</span> <span class="keyword">THEN</span> <span class="number">1</span> <span class="keyword">ELSE</span> <span class="number">0</span> <span class="keyword">END</span>) <span class="keyword">AS</span> <span class="string">'error1'</span>,</span><br><span class="line"><span class="keyword">SUM</span>(<span class="keyword">CASE</span> <span class="keyword">WHEN</span> error_id = <span class="number">2</span> <span class="keyword">THEN</span> <span class="number">1</span> <span class="keyword">ELSE</span> <span class="number">0</span> <span class="keyword">END</span>) <span class="keyword">AS</span> <span class="string">'error2'</span>,</span><br><span class="line"><span class="keyword">SUM</span>(<span class="keyword">CASE</span> <span class="keyword">WHEN</span> error_id = <span class="number">3</span> <span class="keyword">THEN</span> <span class="number">1</span> <span class="keyword">ELSE</span> <span class="number">0</span> <span class="keyword">END</span>) <span class="keyword">AS</span> <span class="string">'error3'</span>,</span><br><span class="line">....</span><br><span class="line"><span class="keyword">SUM</span>(<span class="keyword">CASE</span> <span class="keyword">WHEN</span> error_id = n <span class="keyword">THEN</span> <span class="number">1</span> <span class="keyword">ELSE</span> <span class="number">0</span> <span class="keyword">END</span>) <span class="keyword">AS</span> <span class="string">'errorn'</span>,</span><br><span class="line"><span class="keyword">SUM</span>(<span class="number">1</span>) <span class="keyword">AS</span> <span class="string">'total'</span></span><br><span class="line"><span class="keyword">FROM</span> h5log.user_action</span><br><span class="line">	<span class="keyword">INNER</span> <span class="keyword">JOIN</span> h5log.logs <span class="keyword">ON</span> h5log.user_action.log_id = h5log.logs.id</span><br><span class="line"><span class="keyword">WHERE</span> h5log.logs.qsid = <span class="number">100</span></span><br></pre></td></tr></table></figure>
<p>这样通过一次查询就可以得到所以错误出现的次数以及错误出现的总次数，得到结果后再写些除法的代码就能得到各种错误的占比了。</p>
<h4 id="sequelizejs-如何执行原生的-js"><a href="#sequelizejs-如何执行原生的-js" class="headerlink" title="sequelizejs 如何执行原生的 js"></a>sequelizejs 如何执行原生的 js</h4><p>使用 sequelize.query 方法，后面需要带上{ type: sequelize.QueryTypes.SELECT }参数，否则 query 每次返回结果就是一个数组，数据里的第一项才是我们需要的结果。</p>
<figure class="highlight js"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">(<span class="keyword">async</span> <span class="function"><span class="keyword">function</span>(<span class="params"></span>)</span>&#123;&#125;(</span><br><span class="line">    <span class="keyword">let</span> errors = <span class="keyword">await</span> sequelize.query(<span class="string">"SELECT * from h5log.result WHERE type = 0"</span>, &#123; <span class="attr">type</span>: sequelize.QueryTypes.SELECT &#125;)</span><br><span class="line">))</span><br></pre></td></tr></table></figure>
<h4 id="参考资料"><a href="#参考资料" class="headerlink" title="参考资料"></a>参考资料</h4><p><a href="http://www.jb51.net/article/118538.htm" target="_blank" rel="noopener">MySql 版本问题 sql_mode=only_full_group_by 的完美解决方案</a><br><a href="http://www.cnblogs.com/snsdzjlz320/p/5738226.html" target="_blank" rel="noopener">分组查询用法</a></p>

  </div>
  <div class="statement">
    <hr>  
    1. 如果本文帮到了您，不妨点一下右上角的”<i class="fas fa-share-alt"></i>“按钮。<br>
    2. 除非注明，本博文章均为原创，转载请以链接形式标明本文地址。<br>
    3. 如果你有任何疑问，欢迎发送邮件到我的邮箱<a href="mailto:andyliwr@outlook.com">andyliwr@outlook.com<br>
  </div>
</article>


    <div class="blog-post-comments">
        <div id="gitalk_thread">
            <noscript>加载评论需要在浏览器启用 JavaScript 脚本支持。</noscript>
        </div>
    </div>



        
          <div id="footer-post-container">
  <div id="footer-post">

    <div id="nav-footer" style="display: none">
      <ul>
         
          <li><a href="/">首页</a></li>
         
          <li><a href="/archives/">归档</a></li>
         
          <li><a href="/search/">搜索</a></li>
         
          <li><a href="/tags/">标签</a></li>
         
          <li><a href="/others/">其他文章</a></li>
        
      </ul>
    </div>

    <div id="toc-footer" style="display: none">
      <ol class="toc"><li class="toc-item toc-level-4"><a class="toc-link" href="#前言"><span class="toc-number">1.</span> <span class="toc-text">前言</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#MySQL-如何对表中数据进行分组"><span class="toc-number">2.</span> <span class="toc-text">MySQL 如何对表中数据进行分组</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#表结构"><span class="toc-number">3.</span> <span class="toc-text">表结构</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#SQL-日期格式化函数"><span class="toc-number">4.</span> <span class="toc-text">SQL 日期格式化函数</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#如何做每个分类的次数统计"><span class="toc-number">5.</span> <span class="toc-text">如何做每个分类的次数统计</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#sequelizejs-如何执行原生的-js"><span class="toc-number">6.</span> <span class="toc-text">sequelizejs 如何执行原生的 js</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#参考资料"><span class="toc-number">7.</span> <span class="toc-text">参考资料</span></a></li></ol>
    </div>

    <div id="share-footer" style="display: none">
      <ul>
  <li><a class="icon" href="http://www.facebook.com/sharer.php?u=https://andyliwr.github.io/2017/11/08/mysql_query_learning/"><i class="fab fa-facebook fa-lg" aria-hidden="true"></i></a></li>
  <li><a class="icon" href="https://twitter.com/share?url=https://andyliwr.github.io/2017/11/08/mysql_query_learning/&text=mysql查询的小心得"><i class="fab fa-twitter fa-lg" aria-hidden="true"></i></a></li>
  <li><a class="icon" href="http://www.linkedin.com/shareArticle?url=https://andyliwr.github.io/2017/11/08/mysql_query_learning/&title=mysql查询的小心得"><i class="fab fa-linkedin fa-lg" aria-hidden="true"></i></a></li>
  <li><a class="icon" href="https://pinterest.com/pin/create/bookmarklet/?url=https://andyliwr.github.io/2017/11/08/mysql_query_learning/&is_video=false&description=mysql查询的小心得"><i class="fab fa-pinterest fa-lg" aria-hidden="true"></i></a></li>
  <li><a class="icon" href="mailto:?subject=mysql查询的小心得&body=Check out this article: https://andyliwr.github.io/2017/11/08/mysql_query_learning/"><i class="fas fa-envelope fa-lg" aria-hidden="true"></i></a></li>
  <li><a class="icon" href="https://getpocket.com/save?url=https://andyliwr.github.io/2017/11/08/mysql_query_learning/&title=mysql查询的小心得"><i class="fab fa-get-pocket fa-lg" aria-hidden="true"></i></a></li>
  <li><a class="icon" href="http://reddit.com/submit?url=https://andyliwr.github.io/2017/11/08/mysql_query_learning/&title=mysql查询的小心得"><i class="fab fa-reddit fa-lg" aria-hidden="true"></i></a></li>
  <li><a class="icon" href="http://www.stumbleupon.com/submit?url=https://andyliwr.github.io/2017/11/08/mysql_query_learning/&title=mysql查询的小心得"><i class="fab fa-stumbleupon fa-lg" aria-hidden="true"></i></a></li>
  <li><a class="icon" href="http://digg.com/submit?url=https://andyliwr.github.io/2017/11/08/mysql_query_learning/&title=mysql查询的小心得"><i class="fab fa-digg fa-lg" aria-hidden="true"></i></a></li>
  <li><a class="icon" href="http://www.tumblr.com/share/link?url=https://andyliwr.github.io/2017/11/08/mysql_query_learning/&name=mysql查询的小心得&description="><i class="fab fa-tumblr fa-lg" aria-hidden="true"></i></a></li>
</ul>

    </div>

    <div id="actions-footer">
        <a id="menu" class="icon" href="#" onclick="$('#nav-footer').toggle();return false;"><i class="fas fa-bars fa-lg" aria-hidden="true"></i> 菜单</a>
        <a id="toc" class="icon" href="#" onclick="$('#toc-footer').toggle();return false;"><i class="fas fa-list fa-lg" aria-hidden="true"></i> 目录</a>
        <a id="share" class="icon" href="#" onclick="$('#share-footer').toggle();return false;"><i class="fas fa-share-alt fa-lg" aria-hidden="true"></i> 分享</a>
        <a id="top" style="display:none" class="icon" href="#" onclick="$('html, body').animate({ scrollTop: 0 }, 'fast');"><i class="fas fa-chevron-up fa-lg" aria-hidden="true"></i> 返回顶部</a>
    </div>

  </div>
</div>

        
        <footer id="footer">
  <div class="footer-left">
    Copyright &copy; 2020 Dikang Li
  </div>
  <div class="footer-right">
    <nav>
      <ul>
         
          <li><a href="/">首页</a></li>
         
          <li><a href="/archives/">归档</a></li>
         
          <li><a href="/search/">搜索</a></li>
         
          <li><a href="/tags/">标签</a></li>
         
          <li><a href="/others/">其他文章</a></li>
        
      </ul>
    </nav>
  </div>
</footer>

    </div>
</body>
</html>
<!-- styles -->
<link rel="stylesheet" href="/lib/font-awesome/css/all.min.css">
<link rel="stylesheet" href="/lib/justified-gallery/css/justifiedGallery.min.css">
<link rel="stylesheet" href="/lib/fancybox/jquery.fancybox.css">

<link rel="stylesheet" href="https://file.lantingshucheng.com/blog/v3/static/styles/gitalk.css">


<!-- jquery -->
<script src="/lib/jquery/jquery.min.js"></script>
<script src="/lib/justified-gallery/js/jquery.justifiedGallery.min.js"></script>
<script src="/lib/fancybox/jquery.fancybox.pack.js"></script>

<script type="text/javascript" src="https://file.lantingshucheng.com/blog/v3/static/scripts/md5.js"></script>
<script type="text/javascript" src="https://file.lantingshucheng.com/blog/v3/static/scripts/gitalk.min.js"></script>

<script src="/js/main.js"></script>
<!-- search -->

<!-- Google Analytics -->

<!-- Baidu Analytics -->

    <script type="text/javascript">
        var _hmt = _hmt || [];
        (function() {
            var hm = document.createElement("script");
            hm.src = "https://hm.baidu.com/hm.js?7851ca7a86be957be994acb77639a5be";
            var s = document.getElementsByTagName("script")[0];
            s.parentNode.insertBefore(hm, s);
        })();
    </script>

<!-- Disqus Comments -->


  <script type="text/javascript">
    window.onload = function(){
      if (window.location.href.indexOf('andyliwr.github.io') > -1) {
        var gitalk = new Gitalk({
          clientID: 'ad6f70df0c70d2b715f9',
          clientSecret: '40384bee244c1e17dd7c37733d21e9ef669e969c',
          repo: 'Andyliwr.github.io',
          owner: 'Andyliwr',
          admin: ['Andyliwr'],
          id: md5(location.pathname),
          distractionFreeMode: 'true'
        })
        gitalk.render('gitalk_thread')
      }
    }
  </script>


